/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package quanlythuvien;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;
import thuvien.NhaXuatBan;
import thuvien.Sach;
import thuvien.TacGia;
import thuvien.TheLoai;

/**
 *
 * @author Tuyen
 */
public class DBConnect {

    Connection conn;
    Statement st;
    ResultSet result;

    public void ConnectDB() {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/qlythuvien", "root", "tuyen771992");
            st = conn.createStatement();
        } catch (ClassNotFoundException | SQLException ex) {
            Logger.getLogger(DBConnect.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public ResultSet getAccoutByName(String name) {
        String sql = "select*from account where taikhoan='" + name + "'";
        try {
            result = st.executeQuery(sql);
        } catch (SQLException ex) {
            Logger.getLogger(DBConnect.class.getName()).log(Level.SEVERE, null, ex);
        }
        return result;
    }

    public void addNewAccount(String username, String pass, int sdt) {
        try {
            String sql = "insert into account values ('" + username + "','" + pass + "'," + sdt + ")";
            st.executeUpdate(sql);
        } catch (SQLException ex) {
            Logger.getLogger(DBConnect.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public void addNewDocGia(String tenDG, int gioitinh, String quequan, int sdt, String email) {
        try {
            String sql = "insert into docgia values ('" + tenDG + "'," + gioitinh + ",'" + quequan + "'," + sdt + ",'" + email + "')";
            st.executeUpdate(sql);
        } catch (SQLException ex) {
            Logger.getLogger(DBConnect.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public ResultSet getAllSach() {
        String sql = "select*from sach";
        try {
            result = st.executeQuery(sql);
        } catch (SQLException ex) {
            Logger.getLogger(DBConnect.class.getName()).log(Level.SEVERE, null, ex);
        }
        return result;
    }

    public Sach getSachByName(String name) throws SQLException {
        String sql = "select*from sach where tenSach='" + name + "'";
        result = st.executeQuery(sql);
        result.first();
        Sach sach = new Sach(result.getInt(1), result.getString(2), result.getString(3), result.getInt(4), result.getInt(5),
                result.getInt(6), result.getInt(7));
        return sach;
    }

    public TheLoai getTheLoaiByID(int id) throws SQLException {

        String sql = "select*from theloai where maTheLoai=" + id;
        result = st.executeQuery(sql);
        result.first();
        TheLoai tl = new TheLoai(id,result.getString(2));
        return tl;
    }

    public TacGia getTacGiaByID(int id) throws SQLException {
        String sql = "select*from tacgia where maTacGia=" + id;
        result = st.executeQuery(sql);
        result.first();
        TacGia tg = new TacGia(id, result.getString(2), result.getString(3));
        return tg;
    }

    public NhaXuatBan getNXBByID(int id) throws SQLException {
        String sql = "select*from nhaxuatban where maNXB=" + id;
        result = st.executeQuery(sql);
        result.first();
        NhaXuatBan nxb = new NhaXuatBan(id, result.getString(2), result.getString(3));
        return nxb;
    }
}
